package com.genius.workflow.util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Validate;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.adminmanager.other.entity.RequestJson;
import com.genius.workflow.config.DefaultConfiguration;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.page.PageResult;
import com.genius.workflow.page.Points;
import com.genius.workflow.page.SqlLang;



import com.genius.workflow.page.SearchResult;

public class DBToolsJiuKu {

	public static enum DBType{
		MySql,
		Oracle,
		SqlServer
	}
	/**
	 * 普通的sql查询语句
	 * @param field
	 * @param table
	 * @return
	 */
	public static String getSelectSql(String field,String table){

		String newTable = "";
		int selectIndexOf = table.toLowerCase().indexOf("select ");
		if(selectIndexOf>=0){
			newTable = "("+table+")ta1";
		}else{
			newTable = table;
		}
		String sql = "select "+field+" from "+newTable;
		return sql;
	}
	/**
	 * 普通的sql查询语句 带where语句
	 * @param field
	 * @param table
	 * @return
	 */
	public static String getSelectAndWhereSql(String field,String table,String where){

		String newTable = "";
		int selectIndexOf = table.toLowerCase().indexOf("select ");
		if(selectIndexOf>=0){
			newTable = "("+table+")ta1";
		}else{
			newTable = table;
		}
		String sql = "select "+field+" from "+newTable+getWhereSql(where);
		return sql;
	}
	/**
	 * 查询语句把参数自动生成
	 * @param json  参数值合集
	 * @return
	 */
	public static String[] getSelectParms(String whereField,JSONObject json){
		return getParms(whereField, json);
	}
	public static String getInsertSql(String field,String table){
		String spilStr = DefaultConfiguration.separator_douhao;
		if(field.contains(DefaultConfiguration.separator_char)){
			spilStr = DefaultConfiguration.separator_char;
		}
		String [] fields = field.split(spilStr);
		String insertField = "";
		for(String f:fields){
			if(f.contains("=")){
				String spil [] = f.split("=");
				insertField += spil[0]+",";
			}else{
				insertField += f+",";
			}
		}
		insertField = insertField.substring(0, insertField.lastIndexOf(","));
		String sql = "insert into "+table+"("+insertField+")"+getInsertSqlValues(field);
		return sql;
	}
	public static String getReplaceInsertSql(String field,String table){
		String sql = "replace into "+table+"("+field+")"+getInsertSqlValues(field);
		return sql;
	}
	private static String getInsertSqlValues(String field){
		String spilStr = DefaultConfiguration.separator_douhao;
		if(field.contains(DefaultConfiguration.separator_char)){
			spilStr = DefaultConfiguration.separator_char;
		}
		String values = " values(";
		String [] fields = field.split(spilStr);
		for(int i=0;i<fields.length;i++){
			String f = fields[i];
			if(f.contains("=")){
				values += f.split("=")[1]+",";
			}else{
				values += "?,";
			}
		}
		values = values.substring(0, values.lastIndexOf(","))+")";
		return values;
	}
	/**
	 * 生成所有inser语句需要的参数
	 * @param field
	 * @param json
	 * @return
	 */
	public static String[] getInsertParms(String field,JSONObject json){
		return getParms(field, json);
	}
	public static List<String[]> getInsertListParms(String field,JSONArray arr){
		ArrayList<String[]> result = new ArrayList<>();
		for(int index=0;index<arr.size();index++){
			result.add(getInsertParms(field, arr.getJSONObject(index)));
		}
		return result;
	}
	public static String[] getUpdateParms(String field,JSONObject json){
		return getParms(field, json);
	}
	/**
	 * 生成所有update语句需要的参数
	 * @param field
	 * @param json
	 * @param whereField  如果有where条件，把条件的字段也最加上，前提是json中包含有whereField的字段
	 * @return
	 */
	public static String[] getUpdateAndWhereParms(String field,JSONObject json,Object ...whereField){
		return getParms(field, json,whereField);
	}
	/**
	 * 更新多条数据时获取条件的参数合集
	 * @param field
	 * @param arr
	 * @param whereField
	 * @return
	 */
	public static List<String[]> getUpdateListParms(String field,JSONArray arr,Object ...whereField){
		ArrayList<String[]> result = new ArrayList<>();
		for(int index=0;index<arr.size();index++){
			System.out.println(arr.get(index));
			result.add(getUpdateAndWhereParms(field, arr.getJSONObject(index), whereField));
		}
		return result;
	}

	public static String getDelSql(String table){
		String sql = "delete from "+table;
		return sql;
	}
	public static String getDelSqlAndWhere(String table,String whereField){
		String sql = "delete from "+table+getWhereSql(whereField);
		return sql;
	}
	public static String[] getDeleteParms(String field,JSONObject json){
		return getParms(field, json);
	}
	public static List<String[]> getDeleteListParms(String field,JSONArray arr){
		ArrayList<String[]> result = new ArrayList<>();
		for(int index=0;index<arr.size();index++){
			result.add(getDeleteParms(field, arr.getJSONObject(index)));
		}
		return result;
	}
	private static String[] JoinNewArr(String str,String [] arr){
		String [] newArr = new String[arr.length+1];
		for(int i=0;i<arr.length;i++){
			newArr[i] = arr[i];
		}
		if(arr.length==0)
			newArr[0] = str;
		else
			newArr[arr.length] = str;
		return newArr;
	}
	/**
	 *
	 * @param field
	 * @param json  拼接的数据参数
	 * @param whereField 如果不为空说明是有where语句的参数，追加之的操作
	 * @return
	 */
	private static String[] getParms(String field,JSONObject json,Object ...whereField){
		String spilStr = DefaultConfiguration.separator_douhao;
		if(field.contains(DefaultConfiguration.separator_char)){
			spilStr = DefaultConfiguration.separator_char;
		}
		String [] trans_fields = field.split(spilStr);
		String [] fields = new String[0];
		for(String editField:trans_fields){
			if(!editField.contains("=")|| editField.contains("?")){
				if(editField.contains("?")){
					editField = editField.split("=")[0];
				}
				fields = JoinNewArr(editField, fields);
			}
		}
		int length = fields.length;
		String [] whereFieldArr = null;
		if(whereField.length !=0){
			String whereFieldStr = whereField[0].toString();
			whereFieldArr = whereFieldStr.split(spilStr);
			length = length+whereFieldArr.length;
		}
		String [] result = new String[length];
		for(int index=0;index<fields.length;index++){
			result[index] = json.containsKey(fields[index])?json.getString(fields[index]):"";
		}
		if(whereFieldArr !=null ){
			for(int index=0;index<whereFieldArr.length;index++){
				String key = (String)whereFieldArr[index];
				result[fields.length+index] = json.containsKey(key)?json.getString(key):"";
			}
		}
		return result;
	}
	/**
	 *
	 * @param field
	 * @return
	 */
	private static String getUpdateSetSql(String field){
		String spilStr = DefaultConfiguration.separator_douhao;
		if(field.contains(DefaultConfiguration.separator_char)){
			spilStr = DefaultConfiguration.separator_char;
		}
		String [] fields = field.split(spilStr);
		String set = " set ";
		for(String f:fields){
			if(f.contains("=")){
				set += f+",";
			}else{
				set += f+"=?,";
			}
		}
		set = set.substring(0, set.lastIndexOf(","));
		return set;
	}
	/**
	 * 获取update的语句
	 * @param field
	 * @param table
	 * @return
	 */
	public static String getUpdateSql(String field,String table){
		String sql = "update "+table+getUpdateSetSql(field);
		return sql;
	}
	/**
	 * 获取update的语句
	 * @param field
	 * @param table
	 * @param whereField  where条件也加上
	 * @return
	 */
	public static String getUpdateAndWhereSql(String field,String table,String whereField){
		String sql = "update "+table+getUpdateSetSql(field)+getWhereSql(whereField);
		return sql;
	}
	private static String getWhereSql(String field){
		String [] fields = field.split(DefaultConfiguration.separator_douhao);
		String where = " where ";
		for(String f:fields){
			where += f+"=? and ";
		}
		where = where.substring(0, where.lastIndexOf("and"));
		return where;
	}
	/**
	 * 插入数据操作
	 * @param insertField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static int[] insert(String insertField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException {
		String insertSql = DBToolsJiuKu.getInsertSql(insertField, table);
		String [] parms = DBToolsJiuKu.getInsertParms(insertField, json);
		return insert(insertSql,parms);
	}
	public static int [] insert(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException {
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		return executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 批量插入数据操作
	 * @param insertField
	 * @param table
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void insertBatch(String insertField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException {
		String insertSql = DBToolsJiuKu.getInsertSql(insertField, table);
		List<String[]> parms = DBToolsJiuKu.getInsertListParms(insertField, arr);
		insertBatch(insertSql, parms);
	}
	public static void insertBatch(String sql,List<String[]> parms) throws ConnectionException, DBClosedException, SQLException {
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	public static int [] executeBatch(LinkedList<SQLBean>beanList) throws ConnectionException, DBClosedException, SQLException{
		return DBUtilsJiuKu.executeBatch(beanList);
	}
	public static int [] execute(SQLBean bean) throws ConnectionException, DBClosedException, SQLException{
		return DBUtilsJiuKu.execute(bean);
	}
	/**
	 * 更新操作
	 * @param updateField
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void update(String updateField,String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		String updateSql = DBToolsJiuKu.getUpdateAndWhereSql(updateField, table, whereField);
		String [] parms = DBToolsJiuKu.getUpdateAndWhereParms(updateField, json,whereField);
		System.out.println(updateSql);
		for (int i = 0; i < parms.length; i++) {
			//System.out.println(i+"---"+parms[i]);
		}
		update(updateSql, parms);
	}
	public static void update(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 批量更新操作
	 * @param updateField
	 * @param whereField
	 * @param table
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void updateBatch(String updateField,String whereField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException{
		String updateSql = DBToolsJiuKu.getUpdateAndWhereSql(updateField, table, whereField);
		List<String[]> parms = DBToolsJiuKu.getUpdateListParms(updateField, arr, whereField);
		updateBatch(updateSql, parms);
	}
	public static void updateBatch(String sql,List<String[]> parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 删除数据操作
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void delete(String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		if(json.containsKey(RequestJson.delData)){
			deleteBach(whereField, table, json.getJSONArray(RequestJson.delData));
		}else{
			String[] parms = DBToolsJiuKu.getDeleteParms(whereField, json);
			String delSql = DBToolsJiuKu.getDelSqlAndWhere(table, whereField);
			SQLBean bean = new SQLBean();
			bean.setSql(delSql);
			bean.setParms(parms);
			executeBatch(new LinkedList(Arrays.asList(bean)));
		}
	}
	public static void delete(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 删除数据操作
	 * @param whereField
	 * @param table
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void deleteBach(String whereField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException{
		List<String[]>parms = DBToolsJiuKu.getDeleteListParms(whereField, arr);
		String delSql = DBToolsJiuKu.getDelSqlAndWhere(table, whereField);
		deleteBach(delSql, parms);
	}
	public static void deleteBach(String sql,List<String[]>parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 查询操作
	 * @param selectField
	 * @param table
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject select(String selectField,String table) throws ConnectionException, DBClosedException, SQLException{
		return select(selectField, table, null,null);
	}
	public static JSONObject select(String sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
		return DBUtilsJiuKu.select(sql, params);
	}
	/**
	 * 查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @param whereField where条件字段
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject select(String selectField,String table,String whereField,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		String selectSql = "";
		JSONObject result = null;
		if(whereField == null){
			selectSql = getSelectSql(selectField, table);
			result = DBUtilsJiuKu.select(selectSql);
		}else{
			selectSql = getSelectAndWhereSql(selectField, table, whereField);
			String [] parms = DBToolsJiuKu.getSelectParms(whereField, json);
			result = DBUtilsJiuKu.select(selectSql,parms);
		}
		return result;
	}
	/**
	 * 查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONArray selectArr(String selectField,String table) throws ConnectionException, DBClosedException, SQLException{

		return selectArr(selectField, table, null, null);
	}
	/**
	 * 批量查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @param whereField where条件字段
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONArray selectArr(String selectField,String table,String whereField,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		String selectSql = "";
		JSONArray arr = null;
		if(whereField == null){
			selectSql = getSelectSql(selectField, table);
			arr = DBUtilsJiuKu.selectArr(selectSql);
		}else{
			selectSql = getSelectAndWhereSql(selectField, table, whereField);
			System.out.println(selectSql);
			String[] parms = getSelectParms(whereField, json);
			arr = DBUtilsJiuKu.selectArr(selectSql,parms);
		}
		return arr;
	}
	public static JSONArray selectArr(String sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
		return DBUtilsJiuKu.selectArr(sql,params);
	}


	public static SqlLang selectByMySql(String headField,String select,int page,int limit,String ascField,String asc,String where){
		String lowerCaseSelect = select.toLowerCase();

		String orderBy = "";
		if(asc !=null && ascField!=null){
			orderBy = " order by "+ascField+" "+asc;
		}
		Points points = isContains$key(lowerCaseSelect);
		String selectHead = "";

		if(where.length()>0){
			if(points.getContains$()){
				selectHead = select.replace(points.get$searchKey(),points.getSearchKey()+where)+orderBy;
			}else{
				String connect = points.getContainsWhere()?" and ":"";
				String whereConnect = " where ";//不包含where语句，则要加上where
				int lastIndex = points.getContainsWhere()?points.getEndIndexPrimaryKey():points.getStartIndexPrimaryKey();
				if(points.getStartIndexPrimaryKey()>0)
					selectHead = select.substring(0,points.getStartIndexPrimaryKey())+whereConnect+where+connect+select.substring(lastIndex)+orderBy;
				else
					selectHead = select+whereConnect+where+orderBy;
			}
		}else{
			selectHead = select+orderBy;
		}
		String limitPage = "";
		if(page != 0 && limit !=0){
			limitPage = " limit "+(page-1)*limit+","+limit;
		}
		selectHead = selectHead;
		if(headField != null && !"".equals(headField)){
			String sql = selectHead;
			selectHead = "select SQL_CALC_FOUND_ROWS "+headField+" from ("+sql+")a"+limitPage;
		}else{
			String sql = selectHead;
			headField = DBTools2.getSelectFieldFromSqlStr(selectHead);
			selectHead = "select SQL_CALC_FOUND_ROWS "+headField+" from ("+sql+")a"+limitPage;
		}

		SqlLang sqlStatements = new SqlLang();
		sqlStatements.setPageSql(selectHead);
		sqlStatements.setSourceSql(selectHead);
		return sqlStatements;
	}
	public static SqlLang selectBySqlServer(String headField,String select,int page,int limit,String ascField,String asc,String where){
		String lowerCaseSelect = select.toLowerCase();
		Points points = isContains$key(lowerCaseSelect);
		String selectHead = "";

		if(where.length()>0){
			if(points.getContains$()){
				selectHead = select.replace(points.get$searchKey(),points.getSearchKey()+where);
			}else{
				String connect = points.getContainsWhere()?" and ":"";
				String whereConnect = " where ";//不包含where语句，则要加上where
				int lastIndex = points.getContainsWhere()?points.getEndIndexPrimaryKey():points.getStartIndexPrimaryKey();
				if(points.getStartIndexPrimaryKey()>0)
					selectHead = select.substring(0,points.getStartIndexPrimaryKey())+whereConnect+where+connect+select.substring(lastIndex);
				else
					selectHead = select+whereConnect+where;
			}
		}else{
			selectHead = select;
		}
		if(headField != null && !"".equals(headField)){
			String sql = select+where;
			selectHead = "select "+headField+" from ("+sql+")a";
		}else{
			String sql = select+where;
			headField = DBTools2.getSelectFieldFromSqlStr(selectHead);
			selectHead = "select "+headField+" from ("+sql+")a";
		}
		if(ascField == null){
			ascField = headField.split(",")[0];
			asc = "asc";
		}
		String limitPage = "";
		if(page != 0 && limit !=0){
			String pagesize = limit+"";
			String toPage = (page-1)*limit+"";
			limitPage = "select top "+pagesize+" * from (select row_number()over(order by "+ascField+" "+asc+") as rownumber,* from ("+selectHead+")aa) temp_row where rownumber> "+toPage;
		}
		String resultSql = "";
		if(StringUtils.isNotBlank(limitPage))
			resultSql = limitPage;
		else
			resultSql = selectHead;
		SqlLang sqlStatements = new SqlLang();
		sqlStatements.setPageSql(resultSql);
		sqlStatements.setSourceSql(selectHead);
		return sqlStatements;
	}
	public static SqlLang selectByOracle(String headField,String select,int page,int limit,String ascField,String asc,String where){
		String orderBy = "";
		if(asc !=null){
			orderBy = " order by "+ascField+" "+asc;
		}
		String lowerCaseSelect = select.toLowerCase();
		Points points = isContains$key(lowerCaseSelect);

		String selectHead = "";
		if(where.length()>0){
			if(points.getContains$()){
				selectHead = select.replace(points.get$searchKey(),points.getSearchKey()+where)+orderBy;
			}else{
				String connect = points.getContainsWhere()?" and ":"";
				String whereConnect = " where ";//不包含where语句，则要加上where
				int lastIndex = points.getContainsWhere()?points.getEndIndexPrimaryKey():points.getStartIndexPrimaryKey();
				if(points.getStartIndexPrimaryKey()>0)
					selectHead = select.substring(0,points.getStartIndexPrimaryKey())+whereConnect+where+connect+select.substring(lastIndex)+orderBy;
				else
					selectHead = select+whereConnect+where+orderBy;
			}
		}else{
			selectHead = select+orderBy;
		}

		if(headField != null && !"".equals(headField)){
			String sql = selectHead;
			selectHead = "select "+headField+" from ("+sql+")a";
		}else{
			String sql = selectHead;
			headField = DBTools2.getSelectFieldFromSqlStr(selectHead);
			selectHead = "select "+headField+" from ("+sql+")a";
		}
		String limitPage = "";
		if(page != 0 && limit !=0){
			String start = (page-1)*limit+"";
			String end = page*limit+"";
			limitPage = "select "+headField+" from (select rownum as rowno,t.* from ("+selectHead+")t where rownum<="+end+")a where a.rowno>"+start;
		}
		String resultSql = "";
		if(StringUtils.isNotBlank(limitPage))
			resultSql = limitPage;
		else
			resultSql = selectHead;
		SqlLang sqlStatements = new SqlLang();
		sqlStatements.setPageSql(resultSql);
		sqlStatements.setSourceSql(selectHead);
		return sqlStatements;
	}
	public static String remove$(String select){
		String rex = "\\$(?:\\{)([^\\s]*)(?:\\})";
		Pattern pattern = Pattern.compile(rex);
		Matcher matcher = pattern.matcher(select);
		while(matcher.find()){
			select = select.replace(matcher.group(),"");
		}
		return select;
	}
	private static Points isContains$key(String lowerCaseSelect){
		Points points = new Points();
		String rex = "\\$(?:\\{)([^\\s]*)(?:\\})";
		Pattern pattern = Pattern.compile(rex);
		Matcher matcher = pattern.matcher(lowerCaseSelect);
		while(matcher.find()){
			points.setIsContains$(true);
			points.set$searchKey(matcher.group(0));
			points.setSearchKey(matcher.group(1)+" ");
		}
		if(!points.getContains$()){
			points = isContainsPrimaryKey(lowerCaseSelect,points);
		}
		return points;
	}

	private static Points searchPointsPrimarKey(String lowerCaseSelect,String key,Points points){
		String rex = "(?:\\s|\\t|\\n)"+key+"(?:\\s|\\t|\\n)";
		Pattern pattern = Pattern.compile(rex);
		Matcher matcher = pattern.matcher(lowerCaseSelect);
		String lastStr = "";
		while(matcher.find()){
			lastStr = matcher.group();
		}
		int indexLength = "".equals(lastStr)?0:lowerCaseSelect.lastIndexOf(lastStr);
		if(indexLength>=0){
			if(lastStr.contains("where"))
				points.setIsContainsWhere(true);
			points.setStartIndexPrimaryKey(indexLength);
			points.setEndIndexPrimaryKey(indexLength+lastStr.length());
			return points;
		}
		return points;
	}
	public static Points isContainsPrimaryKey(String lowerCaseSelect,Points points){

		//查看是否包含group by
		for(String key:points.getPrimaryKey()){
			points = searchPointsPrimarKey(lowerCaseSelect,key,points);
			if(points.getStartIndexPrimaryKey()>0)
				break;
		}
		return points;
	}


	public static SearchResult getWhereFieldBySearch(JSONObject json){
		JSONObject resultJson = new JSONObject();
		String searchWhere = "";
		if(json.containsKey(PageResult.search)){
			String lastStr = "";
			try {
				
				JSONArray arr = json.getJSONArray(PageResult.search);
				for(int index=0;index<arr.size();index++){
					
					if(arr.getJSONObject(index) != null){
					SearchResult parseSarch = parseSearch(arr.getJSONObject(index), lastStr, resultJson, searchWhere);
					//如果第一个不为空字符串，插入
						if("".equals(lastStr)){
							lastStr = parseSarch.getLastStr();
						}
						searchWhere = parseSarch.getSearchWhere();
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
				SearchResult parseSarch =parseSearch(json.getJSONObject(PageResult.search), lastStr, resultJson, searchWhere);
				lastStr = parseSarch.getLastStr();
				searchWhere += parseSarch.getSearchWhere();
			}
			if(searchWhere.length()!=0){
				
//				searchWhere = searchWhere.substring(0,searchWhere.lastIndexOf(lastStr));

			searchWhere = searchWhere.substring(searchWhere.indexOf(lastStr)+lastStr.length(),searchWhere.length());
             
	//		System.out.println(searchWhere+"---------ε=(´ο｀*)))唉---");
			}

		}
		SearchResult searchResult = new SearchResult();
		searchResult.setJson(resultJson);
		searchResult.setSearchWhere(searchWhere);
		return searchResult;
	}
	
//	private static SearchResult parseSearch(JSONObject search,String lastStr,JSONObject resultJson,String searchWhere){
//		SearchResult searchResult = new SearchResult();
//		String conditionField = search.getString("conditionField");//查询字段
//		String conditionValue = search.getString("conditionValue");//查询值
//		String conditionOption = search.getString("conditionOption").toLowerCase();//查询符合如 = like and < <= >  >=
//		String logicalOperator = search.getString("logicalOperator");//左右关联条件如 and  or
//		logicalOperator = logicalOperator==null?"":logicalOperator;
//
//		if(conditionValue != null && StringUtils.isNotBlank(conditionValue)){
//			if(conditionOption.equals("in")){
//				String specialStr = GeniusJsonUtil.getSpecialStrBy(conditionValue);
//				searchWhere += conditionField+" "+conditionOption+" ("+specialStr+") "+logicalOperator+" ";
//				String [] specialArr = conditionValue.split(",");
//				int length = specialArr.length;
//				for(int i=1;i<=length;i++){
//					resultJson.put(conditionField+i, specialArr[i-1]);
//				}
//			}else{
//				resultJson.put(conditionField, conditionOption.equals("like")&&!conditionValue.contains("%")?"%"+conditionValue+"%":conditionValue);
//				searchWhere += conditionField+" "+conditionOption+" ? "+logicalOperator+" ";
//
//			}
//		}else{
//			//{"conditionField":"aa","conditionValueLeft":conditionValueLeft,"logicalOperator":">"}
//			//{"conditionField":"aa","conditionValueRight":conditionValueRight,"logicalOperator":"<"}
//			String conditionValueLeft = search.getString("conditionValueLeft");//当为区间查询时左边部分，右边部分
//			if(conditionValueLeft != null && StringUtils.isNotBlank(conditionValueLeft)){
//				logicalOperator = "and";
//				String conditionOptionLeft = search.getString("conditionOptionLeft");//左边查询的条件
//				if(conditionOptionLeft == null){
//					Validate.notNull(conditionOptionLeft, "左边查询条件不能为空");
//				}
//				searchWhere += conditionField+" "+conditionOptionLeft+" ? "+logicalOperator+" ";
//				JSONObject leftAndRight = null;
//				if(resultJson.containsKey(conditionField)){
//					leftAndRight = resultJson.getJSONObject(conditionField);
//				}else{
//					leftAndRight = new JSONObject();
//				}
//				leftAndRight.put("conditionValueLeft", conditionValueLeft);
//				resultJson.put(conditionField, leftAndRight);
//			}
//			String conditionValueRight = search.getString("conditionValueRight");
//			if(conditionValueRight != null && StringUtils.isNotBlank(conditionValueRight)){
//				String conditionOptionRight = search.getString("conditionOptionRight");//左边查询的条件
//				logicalOperator = "and";
//				if(conditionOptionRight == null){
//					Validate.notNull(conditionOptionRight, "右边边查询条件不能为空");
//				}
//				searchWhere += conditionField+" "+conditionOptionRight+" ? "+logicalOperator+" ";
//				JSONObject leftAndRight = null;
//				if(resultJson.containsKey(conditionField)){
//					leftAndRight = resultJson.getJSONObject(conditionField);
//				}else{
//					leftAndRight = new JSONObject();
//				}
//				leftAndRight.put("conditionValueRight", conditionValueRight);
//				resultJson.put(conditionField, leftAndRight);
//			}
//		}
//		lastStr = logicalOperator;
//		searchResult.setSearchWhere(searchWhere);
//		searchResult.setLastStr(lastStr);
//		return searchResult;
//	}
	private static SearchResult parseSearch(JSONObject search,String lastStr,JSONObject resultJson,String searchWhere){
		SearchResult searchResult = new SearchResult();
		String conditionField = search.getString("conditionField");//查询字段
		String conditionValue = search.getString("conditionValue");//查询值
		String conditionOption = search.getString("conditionOption");//查询符合如 = like and < <= >  >=
		String logicalOperator = search.getString("logicalOperator");//左右关联条件如 and  or
		logicalOperator = logicalOperator==null?"":logicalOperator;

		if(conditionValue != null && StringUtils.isNotBlank(conditionValue)){
			if(conditionOption.equals("in")){
				String specialStr = GeniusJsonUtil.getSpecialStrBy(conditionValue);
				searchWhere += logicalOperator+" "+conditionField+" "+conditionOption+" ("+specialStr+") ";
				String [] specialArr = conditionValue.split(",");
				int length = specialArr.length;
				for(int i=1;i<=length;i++){
					resultJson.put(conditionField+i, specialArr[i-1]);
				}
			}else{
				resultJson.put(conditionField, conditionOption.equals("like")&&!conditionValue.contains("%")?"%"+conditionValue+"%":conditionValue);
				searchWhere += logicalOperator+" "+conditionField+" "+conditionOption+" ? ";

			}
		}else{
			//{"conditionField":"aa","conditionValueLeft":conditionValueLeft,"logicalOperator":">"}
			//{"conditionField":"aa","conditionValueRight":conditionValueRight,"logicalOperator":"<"}
			String conditionValueLeft = search.getString("conditionValueLeft");//当为区间查询时左边部分，右边部分
			if(conditionValueLeft != null && StringUtils.isNotBlank(conditionValueLeft)){
				String logicalOperatorLeftRight = search.getString("logicalOperatorLeftRight")==null?"and":search.getString("logicalOperatorLeftRight");
				String conditionOptionLeft = search.getString("conditionOptionLeft");//左边查询的条件
				if(conditionOptionLeft == null){
					Validate.notNull(conditionOptionLeft, "左边查询条件不能为空");
				}
				String conditionFieldLeft = search.getString("conditionFieldLeft");
				//没有，则使用统一的Field
				if(conditionFieldLeft == null){
					conditionFieldLeft = conditionField;
					JSONObject leftAndRight = null;
					if(resultJson.containsKey(conditionField)){
						leftAndRight = resultJson.getJSONObject(conditionField);
					}else{
						leftAndRight = new JSONObject();
					}
					leftAndRight.put("conditionValueLeft", conditionValueLeft);
					resultJson.put(conditionField, leftAndRight);
				}else{
					resultJson.put(conditionFieldLeft, conditionValueLeft);
				}
				searchWhere += logicalOperator+" "+"( "+conditionFieldLeft+" "+conditionOptionLeft+" ? "+logicalOperatorLeftRight+" ";
				
			}
			String conditionValueRight = search.getString("conditionValueRight");
			if(conditionValueRight != null && StringUtils.isNotBlank(conditionValueRight)){
				String conditionOptionRight = search.getString("conditionOptionRight");//左边查询的条件
//				logicalOperator = "and";
				//没有，则使用统一的Field
				if(conditionOptionRight == null){
					Validate.notNull(conditionOptionRight, "右边边查询条件不能为空");
				}
				String conditionFieldRight = search.getString("conditionFieldRight");
				if(conditionFieldRight == null){
					conditionFieldRight = conditionField;
					JSONObject leftAndRight = null;
					if(resultJson.containsKey(conditionField)){
						leftAndRight = resultJson.getJSONObject(conditionField);
					}else{
						leftAndRight = new JSONObject();
					}
					leftAndRight.put("conditionValueRight", conditionValueRight);
					resultJson.put(conditionField, leftAndRight);
				}else{
					resultJson.put(conditionFieldRight, conditionValueRight);
				}
				searchWhere += conditionFieldRight+" "+conditionOptionRight+" ?) ";
				
			}
		}
		lastStr = logicalOperator;
		searchResult.setSearchWhere(searchWhere);
		searchResult.setLastStr(lastStr);
		return searchResult;
	}
	/**
	 * 这个是对layui的高级查询控件做的筛选
	 * @param json
	 * @return
	 */
	public static SearchResult getWhereFieldByHighSearch(JSONObject json){
		String searchWhere = " ";
		JSONObject resultJson = new JSONObject();
		SearchResult searchResult = new SearchResult();
		if(GeniusJsonUtil.JsonKeyContainsStr(json, PageResult.highSearch)){
			if(GeniusJsonUtil.JsonKeyContainsStr(json, PageResult.highSearch)){
				JSONArray search = GeniusJsonUtil.parseHighSearchJson(json);
				String lastStr = "";
				for(int index=0;index<search.size();index++){
					JSONObject searchJson = search.getJSONObject(index);
					String conditionField = searchJson.getString("conditionField");
					String conditionValue = searchJson.getString("conditionValue");
					String conditionOption = searchJson.getString("conditionOption").toLowerCase();
					String logicalOperator = searchJson.getString("logicalOperator");
					lastStr = logicalOperator;

					String conditionValueLeft = searchJson.getString("conditionValueLeft");
					String conditionValueRight = searchJson.getString("conditionValueRight");

					resultJson.put(conditionField, conditionValue);
					String value = "?";
					if(conditionOption.equals("between")){
						conditionOption = ">=";
						value = "? and "+conditionField+" <=?";
						JSONObject leftAndRight = new JSONObject();
						leftAndRight.put("conditionValueLeft", conditionValueLeft);
						leftAndRight.put("conditionValueRight", conditionValueRight);
						resultJson.put(conditionField, leftAndRight);
					}
					searchWhere += conditionField+" "+conditionOption+" "+value+" "+logicalOperator+" ";
				}
				if(searchWhere.length()!=0)
					searchWhere = searchWhere.substring(0,searchWhere.lastIndexOf(lastStr));
			}
		}
		searchResult.setJson(resultJson);
		searchResult.setSearchWhere(searchWhere);
		return searchResult;
	}
	/**
	 * 判断是否有数据
	 * @param table
	 * @param json
	 * @return
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject getIsExist(String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		String where = " where ";
		for(String keyWord:whereField.split(",")){
			where += keyWord+"=? and ";
		}
		where = where.substring(0, where.lastIndexOf("and"));
		String sql = "select 1 from "+table+where;
		String [] parms = DBToolsJiuKu.getSelectParms(whereField, json);
		return DBUtilsJiuKu.select(sql, parms);
	}

	public static JSONObject getFilling(HashMap<String, String>sqlMap) throws ConnectionException, DBClosedException, SQLException{
		JSONObject	json = new JSONObject();
		for(String key:sqlMap.keySet()){
			JSONArray keyJson = DBUtilsJiuKu.selectArr(sqlMap.get(key));
			json.put(key, keyJson);
		}
		return json;
	}
	public static JSONObject getFilling(String selectField,String whereField,String table,JSONObject json,JSONObject addJson) throws ConnectionException, DBClosedException, SQLException{
		if(StringUtils.isNotBlank(selectField)){
			String editAddSql = DBToolsJiuKu.getSelectAndWhereSql(selectField, table, whereField);
			String [] parms = DBToolsJiuKu.getSelectParms(whereField, json.getJSONObject(PageResult.parameter));
			JSONObject keyJson = DBUtilsJiuKu.select(editAddSql, parms);
			if(keyJson!=null)
				addJson.putAll(keyJson);
		}
		return addJson;
	}
	public static String getSqlInto(JSONArray arr){
		int len = arr.size();
		String result = "";
		for(int i=0;i<len;i++){
			result += "?,";
		}
		if(len !=0 ){
			result = result.substring(0, result.lastIndexOf(","));
		}
		return result;
	}
	public static String getSqlInto(String [] str){
		if(str == null)
			return "";
		int len = str.length;
		String result = "";
		for(int i=0;i<len;i++){
			result += "?,";
		}
		result = result.substring(0, result.lastIndexOf(","));
		return result;
	}
	/**
	 * 解析sql语句中的 =? <=? <? >? >=? in(?) like
	 * @param sql
	 * @return
	 * 后期修正了如果参数格式化的时候如 to_date(aa,'%y-%m-%d')等之类的
	 */
	public static String [] getFieldNameByParameter(String sql){
		String rex = "(?:\\s)(\\w*|\\w*\\.\\w*)(\\s*)(((?:\\=)|(?:\\<)|(?:\\>)|(?:!\\=)|(?:\\<\\=)|(?:\\>\\=)|((\\s*)(like){1}))(\\s*)(?:\\?)|((\\s*)(in){1}(\\s*)\\()(\\s*)(([\\?,]+)||(?:\\?))(\\s*)(?:\\)))|(\\(){1}(\\S*)(\\)){1}(?:\\=)(?:\\?)";
		java.util.regex.Pattern pattern = java.util.regex.Pattern.compile(rex);
		Matcher matcher = pattern.matcher(sql.toLowerCase());
		ArrayList<String> list = new ArrayList<String>();
		while (matcher.find()) {
			String rexx = "((?<=\\()[^\\)]+)";
			java.util.regex.Pattern patterns = java.util.regex.Pattern.compile(rexx);
			Matcher matchers = patterns.matcher(matcher.group(0));
			boolean isContinue = false;
			while (matchers.find()) {
				isContinue = true;
				if(!matchers.group(0).contains("?"))
					list.add(matchers.group(0));

			}
			if(matcher.group(1)!=null){
				String rexxx = "((\\s*)(in){1}(\\s*)\\()(\\s*)(([\\?,]+)||(?:\\?))(\\s*)(?:\\))";
				java.util.regex.Pattern patternss = java.util.regex.Pattern.compile(rexxx);
				Matcher matcherss = patternss.matcher(matcher.group(0));
				boolean isInMore = false;
				while (matcherss.find()) {
					isInMore = true;
					int length = matcherss.group().split(",").length;
					for(int i=1;i<=length;i++){
						list.add(matcher.group(1)+i);
					}
				}
				if(!isInMore)
					list.add(matcher.group(1));
			}
			else{
				if(!isContinue){
					list.add(matcher.group(0));
				}
			}
		}
		return list.toArray(new String[]{});
	}
	public static String [] parsePermeterByField(String [] parmeters,JSONObject json){
		String [] parms = new String[parmeters.length];
		for(int index=0;index<parmeters.length;index++){
			String key = parmeters[index].toLowerCase();
			for(String jsonKey:json.keySet()){
				if(StringUtils.containsIgnoreCase(key, jsonKey.toLowerCase())){
					try {
						//说明是字段相同，区间查询
						JSONObject betweenJson = json.getJSONObject(jsonKey);
						boolean isAdd = false;
						if(betweenJson.containsKey("conditionValueLeft")){
							isAdd = true;
							parms[index] = betweenJson.getString("conditionValueLeft");
						}
						if(betweenJson.containsKey("conditionValueRight")){
							if(isAdd)
								index++;//区间字段，自动增加1位
							parms[index] = betweenJson.getString("conditionValueRight");
						}
					} catch (Exception e) {
						parms[index] = json.getString(jsonKey);
					}
				}
			}
//				Validate.notNull(key, "【"+key+"】参数不能为空");
		}
		return parms;
	}
}
